﻿// ----------------------------------------------------------------------
// <author name="Moses Kehengu"/>
// <copyright file="RegistrationInfo.cs" company="Futures Group International">
//     Copyright statement. All right reserved
// </copyright>
//
// ------------------------------------------------------------------------
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Data.Common;
using DataModels.Common;

namespace GenericTools.ReportProcessing
{
    public class RegistrationInfo
    {
        #region Class Variables
        private string fullname;
        private Language language;
        private string facilityCode;
        private string emailAddress;
        private string facilityName;
        private string senderNum;
        private string registrationText;

        private bool isDRCHCO = false;
        private bool isRRCHO = false;
        private bool isOthers = false;
        private bool isDataClerk = false;
        private bool isSI = false;
        private UserTitle title;

        private int regionID;
        private string regionName;
        private int districtID;
        private string districtName;

        private bool hasError;
        private bool invalid;
        private RegistrationError errorCode;
        private List<int> supportedLanguages = null;
        private int errorTextLabelID = -1;
        private string errorText = String.Empty;
        #endregion

        #region Class Constants
        private const Language DEFAULT_LANGUAGE = Language.KISWAHILI;
        private const int MAX_USERNAME_LENGTH = 254;

        #region Language Selection keywords
        private const string ENGLISH = "english";
        private const string KIINGEREZA = "kiingereza";
        private const string ENG = "eng";
        private const string ENG_ = "eng.";
        
        private const string KISWAHILI = "kiswahili";
        private const string SWAHILI = "swahili";
        private const string SWA = "swa";
        private const string SWA_ = "swa.";
        #endregion

        #region Error Translation Text Label IDs
        private const int INVALID_OBJECT_LABEL = 32;
        private const int FEW_PARAMETERS_LABEL = 27;
        private const int INVALID_FACILITY_LABEL = 28;
        private const int MISSING_SENDER_NUMBER_LABEL = 29;
        private const int SHORT_USERNAME_LABEL = 30;
        private const int LONG_USERNAME_LABEL = 31;
        private const int NO_ERROR_LABEL = 5;
        private const int INVALID_LANGUAGE_LABEL = 33;
        private const int INVALID_REGION_CODE_LABEL = 49;
        private const int INVALID_DISTRICT_CODE_LABEL = 50;
        private const int SENDER_NUMBER_ALREADY_REGISTERED_LABEL = 51;
        private const int UPDATING_NON_REGISTERED_USER_LABEL = 52;
        #endregion
        #endregion

        #region Class Enumerations
        /// <summary>
        /// Registration Errors
        /// </summary>
        public enum RegistrationError
        {
            /// <summary>
            /// No error associated with this object
            /// </summary>
            NO_ERROR = 0,

            /// <summary>
            /// Invalid facility code
            /// </summary>
            INVALID_FACILITY = 1,

            /// <summary>
            /// Missing username
            /// </summary>
            MISSING_USERNAME = 2,

            /// <summary>
            /// Missing language selection
            /// </summary>
            MISSING_LANGUAGE = 3,

            /// <summary>
            /// Missing Facility code
            /// </summary>
            MISSING_FACILITY_CODE = 4,

            /// <summary>
            /// Username is too short, at least two names
            /// </summary>
            SHORT_USERNAME = 5,

            /// <summary>
            /// Username is too long, at most two names with less than 250 characters in total
            /// </summary>
            LONG_USERNAME = 6,

            /// <summary>
            /// Invalid object. This is due to non-argument 
            /// </summary>
            INVALID_OBJECT = 7,

            /// <summary>
            /// Invalid language code
            /// </summary>
            INVALID_LANGUAGE = 8,

            /// <summary>
            /// Parameters are fewer than expected
            /// </summary>
            FEW_PARAMETERS = 9,

            /// <summary>
            /// Registering user cell phone number
            /// </summary>
            MISSING_SENDER_NUMBER = 10,

            /// <summary>
            /// Invalid region code
            /// </summary>
            INVALID_REGION_CODE = 11,

            /// <summary>
            /// Invalid district code
            /// </summary>
            INVALID_DISTRICT_CODE = 12,

            /// <summary>
            /// Sender number is already registered
            /// </summary>
            SENDER_NUMBER_ALREADY_REGISTERED = 13,

            /// <summary>
            /// Updating non-registered user
            /// </summary>
            UPDATING_NON_REGISTERED_USER = 14
        }

        /// <summary>
        /// Supported Languages
        /// </summary>
        public enum Language
        {
            /// <summary>
            /// English Language
            /// </summary>
            ENGLISH = 1,

            /// <summary>
            /// Kiswahili Language - best language ever...!
            /// </summary>
            KISWAHILI = 2
        }

        /// <summary>
        /// Registering user title
        /// </summary>
        public enum UserTitle
        {
            /// <summary>
            /// Data clerk
            /// </summary>
            DATA_CLERK = 1,

            /// <summary>
            /// District Reproductive and Child Health Coordinator
            /// </summary>
            DRCHCO = 2,

            /// <summary>
            /// Region Reproductive and Child Health Coordinator
            /// </summary>
            RRCHCO = 3,

            /// <summary>
            /// Futures Group Stragetic Information Associate
            /// </summary>
            SI = 4,

            /// <summary>
            /// Others
            /// </summary>
            OTHERS = 5
        }
        #endregion

        #region Constructors
        /// <summary>
        /// Creates an invalid registration object
        /// </summary>
        private RegistrationInfo()
        {
            this.fullname = String.Empty;
            this.facilityCode = String.Empty;
            this.facilityName = String.Empty;
            this.senderNum = String.Empty;
            this.registrationText = String.Empty;
            this.emailAddress = String.Empty;
            this.language = DEFAULT_LANGUAGE;
            this.invalid = true;
            this.hasError = true;
            this.errorCode = RegistrationError.INVALID_OBJECT;
            this.errorTextLabelID = INVALID_OBJECT_LABEL;
            this.errorText = Utilities.GetTranslationText("IQSMS", INVALID_OBJECT_LABEL);
        }

        /// <summary>
        /// Creates registration based on registration text registrationText
        /// </summary>
        /// <param name="senderNum">Sender's cell phone number</param>
        /// <param name="registrationText">Registration text from SMS without REG. keyword</param>
        public RegistrationInfo(string senderNum, string registrationText)
        {
            this.hasError = false;
            this.invalid = false;
            this.senderNum = senderNum;
            this.errorCode = RegistrationError.NO_ERROR;
            this.errorTextLabelID = NO_ERROR_LABEL;
            
            //replyMessage.Replace("<USERNAME>", registrationInfo.RegisteredFullname);
            //replyMessage = replyMessage.Replace("<SENDERNUM>", registrationInfo.SenderNum);

            this.errorText = Utilities.GetTranslationText(this.senderNum, NO_ERROR_LABEL);
            this.registrationText = registrationText;

            // Validating sender number
            if (String.IsNullOrEmpty(senderNum))
            {
                this.hasError = true;
                this.invalid = false;
                this.errorCode = RegistrationError.MISSING_SENDER_NUMBER;
                this.errorTextLabelID = MISSING_SENDER_NUMBER_LABEL;
                this.errorText = Utilities.GetTranslationText("IQSMS", MISSING_SENDER_NUMBER_LABEL);
                return;
            }
            // Validating registration text
            try
            {
                ValidateRegistrationText(registrationText);
                if (!this.hasError)
                {
                    string value = this.senderNum;
                    if (this.isDataClerk) value = this.facilityName;
                    if (this.isDRCHCO) value = this.districtName;
                    if (this.isRRCHO) value = this.regionName;
                    if (this.isSI) value = "Strategic Information Associate";

                    this.errorText = this.errorText.Replace("<USERNAME>", this.fullname);
                    this.errorText = this.errorText.Replace("<SENDERNUM>", value);
                }
            }
            catch (Exception ex)
            {
                Utilities.LogError(ex, null);
                this.hasError = true;
                this.invalid = false;
                this.errorCode = RegistrationError.INVALID_OBJECT;
                this.errorTextLabelID = INVALID_OBJECT_LABEL;
                this.errorText = Utilities.GetTranslationText(this.senderNum, INVALID_OBJECT_LABEL);
            }
        }
        #endregion

        #region Private Properties
        public List<int> SupportedLanguages
        {
            get
            {
                if (this.supportedLanguages == null) GetSupportedLanguages();
                return this.supportedLanguages;
            }
        }
        #endregion

        #region Public Methods
        /// <summary>
        /// Register user to the system
        /// </summary>
        /// <returns></returns>
        public bool Register()
        {
            bool status = false;
            if (hasError) status = false;
            else
            {
                bool registered = IsUserRegistered();
                if (registered) status = UpdateUserRegistration();
                else status = RegisterUser();
            }
            return status;
        }

        /// <summary>
        /// Remove user registration details from the system
        /// </summary>
        /// <returns>True on success otherwise false</returns>
        public bool RemoveUserRegistration()
        {
            bool status = true;
            bool registered = IsUserRegistered();
            if (registered) RemoveUser();
            return status;
        }

        public static bool IsUserRegistered(string phoneNumber)
        {
            using (var db = new DataModels.Models.Context.IQSMS_TestDBContext())
            {
                int count = (from u in db.ApprovedSMSCells
                             where u.CellNum == phoneNumber
                             select u).Count();
                return (count == 1);
            }
        }

        public static bool RemoveUserRegistration(string phoneNumber, out bool userExisted)
        {
            bool deleted = false;
            using (var db = new DataModels.Models.Context.IQSMS_TestDBContext())
            {
                var results = from u in db.ApprovedSMSCells
                              where u.CellNum == phoneNumber
                              select u;

                if (results.Count() > 0)
                {
                    userExisted = true;
                    foreach (var user in results)
                    {
                        db.ApprovedSMSCells.Remove(user);
                        deleted = true;
                    }
                    db.SaveChanges();
                }
                else
                {
                    userExisted = false;
                }
            }
            return deleted;
        }
        #endregion

        #region Public Properties
        /// <summary>
        /// Gets sender number used to create this object
        /// </summary>
        public string SenderNum
        {
            get
            {
                return this.senderNum;
            }
        }

        /// <summary>
        /// Gets registration text used to create this object
        /// </summary>
        public string RegistrationText
        {
            get
            {
                return this.registrationText;
            }
        }

        /// <summary>
        /// Gets registration error code/status of this object
        /// </summary>
        public RegistrationError Error
        {
            get
            {
                return this.errorCode;
            }
        }

        /// <summary>
        /// Gets validity status of this object
        /// </summary>
        public bool Valid
        {
            get
            {
                return !invalid;
            }
        }

        /// <summary>
        /// True if the object has registration errors otherwise false
        /// </summary>
        public bool HasError
        {
            get
            {
                return hasError;
            }
        }

        /// <summary>
        /// Gets registered user fullname
        /// </summary>
        public string RegisteredFullname
        {
            get
            {
                return this.fullname;
            }
        }

        /// <summary>
        /// Gets registered user facility name
        /// </summary>
        public string RegisteredFacilityName
        {
            get
            {
                return this.facilityName;
            }
        }

        /// <summary>
        /// Gets registered user facility code
        /// </summary>
        public string FacilityCode
        {
            get
            {
                return this.facilityCode;
            }
        }

        /// <summary>
        /// Translation text label ID associated with the error in this object
        /// </summary>
        public int ErrorTextLabelID
        {
            get
            {
                return this.errorTextLabelID;
            }
        }

        public string ErrorText
        {
            get
            {
                return this.errorText;
            }
        }
        #endregion

        #region Private Helper Methods
        private bool GetSupportedLanguages()
        {
            SqlConnection connection = new SqlConnection();
            try
            {
                connection.ConnectionString = AppConfiguration.DBConnectionString;
                connection.Open();

                SqlCommand command = connection.CreateCommand();
                command.CommandType = CommandType.Text;
                command.CommandText = @"SELECT DISTINCT LangID FROM Translations";

                DataTable dataTable = new DataTable();
                SqlDataReader reader = command.ExecuteReader();
                dataTable.Load(reader);
                reader.Close();
                connection.Close();

                if (supportedLanguages == null) supportedLanguages = new List<int>();
                supportedLanguages.Clear();

                foreach (DataRow row in dataTable.Rows)
                {
                    int language = int.Parse(row["LangID"].ToString());
                    supportedLanguages.Add(language);
                }
                return true;
            }
            catch
            {
                return false;
            }
            finally
            {
                if (connection.State == ConnectionState.Open) connection.Close();
            }
        }

        private bool IsFacilityValid(string facilityCode)
        {
            SqlConnection connection = new SqlConnection();
            try
            {
                connection.ConnectionString = AppConfiguration.DBConnectionString;
                connection.Open();

                SqlCommand command = connection.CreateCommand();
                command.CommandType = CommandType.Text;
                command.CommandText = @"SELECT FacCode, FacName FROM Facilities WHERE FacCode = @FacCode";

                SqlParameter param = command.CreateParameter();
                param.ParameterName = "@FacCode";
                param.DbType = DbType.String;
                param.Value = facilityCode;
                command.Parameters.Add(param);

                DataTable dataTable = new DataTable();
                SqlDataReader reader = command.ExecuteReader();
                dataTable.Load(reader);
                reader.Close();
                connection.Close();

                if (dataTable.Rows.Count == 0) return false;
                else
                {
                    DataRow row = dataTable.Rows[0];
                    this.facilityCode = row["FacCode"].ToString();
                    this.facilityName = row["FacName"].ToString();
                    this.isDRCHCO = false;
                    this.isRRCHO = false;
                    this.isSI = false;
                    this.isOthers = false;
                    this.isDataClerk = true;
                    return true;
                }
            }
            catch
            {
                return false;
            }
            finally
            {
                if (connection.State == ConnectionState.Open) connection.Close();
            }
        }        

        private bool IsFacilityInitialValid(string initial)
        {
            DbCommand command = GenericDataAccess.CreateCommand();
            command.CommandType = CommandType.Text;
            command.CommandText = @"SELECT DISTINCT Region.RegionNo AS RegionID, Region.RegionName, District.DistrictNo, 
                District.District AS [DistrictName] FROM Facilities INNER JOIN Region ON 
                Facilities.Region = Region.RegionNo INNER JOIN District ON Facilities.District = District.DistrictNo 
                WHERE (Facilities.FacCode LIKE @FacCode + '%')";

            DbParameter param = command.CreateParameter();
            param.ParameterName = "@FacCode";
            param.DbType = DbType.String;
            param.Value = initial;
            command.Parameters.Add(param);
            DataTable dataTable = GenericDataAccess.ExecuteSelectCommand(command);
            if (dataTable.Rows.Count > 0)
            {
                DataRow row = dataTable.Rows[0];
                this.regionID = int.Parse(row["RegionID"].ToString());
                bool sameDistrict = true;
                this.districtID = int.Parse(row["DistrictNo"].ToString());
                foreach (DataRow r in dataTable.Rows)
                {
                    int district = int.Parse(r["DistrictNo"].ToString());
                    if (district != districtID)
                    {
                        sameDistrict = false;
                        break;
                    }
                }
                if (sameDistrict)
                {
                    this.districtName = row["DistrictName"].ToString();
                    this.isDRCHCO = true;
                    this.isRRCHO = true;
                    return true;
                }
                else
                {
                    this.regionName = row["RegionName"].ToString();
                    this.isDRCHCO = false;
                    this.isRRCHO = true;
                    return true;
                }
            }
            else
            {
                this.hasError = true;
                this.invalid = false;
                this.errorCode = RegistrationError.INVALID_FACILITY;
                this.errorTextLabelID = INVALID_FACILITY_LABEL;
                this.errorText = Utilities.GetTranslationText(this.senderNum, INVALID_FACILITY_LABEL);
                return false;
            }
        }

        private void ValidateRegistrationText(string registrationText)
        {
            //TODO: Remove more than one space in the registrationText
            if (String.IsNullOrEmpty(registrationText))
            {
                this.hasError = true;
                this.invalid = false;
                this.errorCode = RegistrationError.FEW_PARAMETERS;
                this.errorTextLabelID = FEW_PARAMETERS_LABEL;
                this.errorText = Utilities.GetTranslationText(this.senderNum, FEW_PARAMETERS_LABEL);
                return;
            }

            string[] rawParts = registrationText.Split();
            List<string> lParts = new List<string>();
            foreach (string part in rawParts)
            {
                if (String.IsNullOrEmpty(part)) continue;
                else lParts.Add(part);
            }
            string[] parts = lParts.ToArray<string>();
            if (parts.Length >= 4)
            {
                int namePartsCount = -1;
                string lastWord = parts[parts.Length - 1].ToLower();
                string facilityCode = String.Empty;
                string languageCode = String.Empty;

                int offset = -1;
                bool registered = IsUserRegistered();
                switch (lastWord)
                {
                    case "re":
                    case "resubmit":
                    case "rekebisha":
                    case "marekebisho":
                    case "sahihisha":
                    case "sahihi":
                    case "rudia":
                    case "tumatena":
                    case "tumaupya":
                    case "badili":  
                    case "badilisha":
                        if (registered) offset = 1;
                        else
                        {
                            this.hasError = true;
                            this.invalid = false;
                            this.errorCode = RegistrationError.UPDATING_NON_REGISTERED_USER;
                            this.errorTextLabelID = UPDATING_NON_REGISTERED_USER_LABEL;
                            this.errorText = Utilities.GetTranslationText(this.senderNum, UPDATING_NON_REGISTERED_USER_LABEL);
                            return;
                        }
                        break;

                    default:
                        if (registered)
                        {
                            this.hasError = true;
                            this.invalid = false;
                            this.errorCode = RegistrationError.SENDER_NUMBER_ALREADY_REGISTERED;
                            this.errorTextLabelID = SENDER_NUMBER_ALREADY_REGISTERED_LABEL;
                            this.errorText = Utilities.GetTranslationText(this.senderNum, SENDER_NUMBER_ALREADY_REGISTERED_LABEL);
                            return;
                        }
                        else offset = 0;
                        break;
                }

                string anotherLastWord = parts[parts.Length - offset - 1];
                if (anotherLastWord.Contains("@"))
                {
                    this.emailAddress = parts[parts.Length - offset - 1];
                    facilityCode = parts[parts.Length - offset - 2];
                    languageCode = parts[parts.Length - offset - 3];
                    namePartsCount = parts.Length - offset - 3;
                }
                else
                {
                    facilityCode = parts[parts.Length - offset - 1];
                    languageCode = parts[parts.Length - offset - 2];
                    namePartsCount = parts.Length - offset - 2;
                }
                
                StringBuilder sBuilder = new StringBuilder();
                for (int i = 0; i < namePartsCount; i++)
                {
                    sBuilder.Append(parts[i]);
                    sBuilder.Append(" ");
                }
                string username = sBuilder.ToString().Trim();

                ValidateUsername(username);
                if (hasError) return;

                // Validating facility code
                ValidateFacilityCode(facilityCode);
                if (hasError) return;

                // Validating selected language
                ValidateLanguageSelection(languageCode);
                if (hasError) return;
            }
            else
            {
                hasError = true;
                invalid = false;
                errorCode = RegistrationError.FEW_PARAMETERS;
                this.errorTextLabelID = FEW_PARAMETERS_LABEL;
                this.errorText = Utilities.GetTranslationText(this.senderNum, FEW_PARAMETERS_LABEL);
            }
        }

        private void ValidateLanguageSelection(string languageCode)
        {
            languageCode = languageCode.ToLower();
            switch (languageCode)
            {
                case KISWAHILI:
                case SWAHILI:
                case SWA_:
                case SWA:
                    language = Language.KISWAHILI;
                    break;

                case ENGLISH:
                case KIINGEREZA:
                case ENG_:
                case ENG:
                    language = Language.ENGLISH;
                    break;

                default:
                    hasError = true;
                    invalid = false;
                    errorCode = RegistrationError.INVALID_LANGUAGE;
                    this.errorTextLabelID = INVALID_LANGUAGE_LABEL;
                    this.errorText = Utilities.GetTranslationText(this.senderNum, INVALID_LANGUAGE_LABEL);
                    break;
            }
        }

        private void ValidateFacilityCode(string facilityCode)
        {
            if (facilityCode.Length == 8)
            {
                string lastSixCharacters = facilityCode.Substring(2, 6);
                if (lastSixCharacters == "000000")
                {
                    string RRCHCOCode = facilityCode.Substring(0, 2);
                    bool validRRCHCOCode = IsFacilityInitialValid(RRCHCOCode);
                    if (validRRCHCOCode)
                    {
                        this.isDataClerk = false;
                        this.isDRCHCO = false;
                        this.isRRCHO = true;
                        this.isOthers = false;
                        this.isSI = false;
                        this.facilityCode = facilityCode;
                        return;
                    }
                    else
                    {
                        this.hasError = true;
                        this.invalid = false;
                        this.errorCode = RegistrationError.INVALID_REGION_CODE;
                        this.errorTextLabelID = INVALID_REGION_CODE_LABEL;
                        this.errorText = Utilities.GetTranslationText(this.senderNum, INVALID_REGION_CODE_LABEL);
                        return;
                    }
                }                
                else
                {
                    string lastFourCharacters = facilityCode.Substring(4, 4);
                    if (lastFourCharacters == "0000")
                    {
                        string DRCHCOCode = facilityCode.Substring(0, 4);
                        bool validDRCHCO = IsFacilityInitialValid(DRCHCOCode);
                        if (validDRCHCO)
                        {
                            this.isDataClerk = false;
                            this.isDRCHCO = true;
                            this.isOthers = false;
                            this.isRRCHO = false;
                            this.isSI = false;
                            this.facilityCode = facilityCode;
                            return;
                        }
                        else
                        {
                            this.hasError = true;
                            this.invalid = false;
                            this.errorCode = RegistrationError.INVALID_DISTRICT_CODE;
                            this.errorTextLabelID = INVALID_DISTRICT_CODE_LABEL;
                            this.errorText = Utilities.GetTranslationText(this.senderNum, INVALID_DISTRICT_CODE_LABEL);
                            return;
                        }
                    }
                    else
                    {
                        bool validFaciltiyCode = IsFacilityValid(facilityCode);
                        if (validFaciltiyCode)
                        {
                            this.isDataClerk = true;
                            this.isDRCHCO = false;
                            this.isRRCHO = false;
                            this.isOthers = false;
                            this.isSI = false;
                            this.facilityCode = facilityCode;
                            return;
                        }
                        else
                        {
                            this.hasError = true;
                            this.invalid = false;
                            this.errorCode = RegistrationError.INVALID_FACILITY;
                            this.errorTextLabelID = INVALID_FACILITY_LABEL;
                            this.errorText = Utilities.GetTranslationText(this.senderNum, INVALID_FACILITY_LABEL);
                            return;
                        }
                    }
                }
            }
            else
            {
                string code = facilityCode.ToLower();
                switch (code)
                {
                    case "si":
                        this.isSI = true;
                        this.isDataClerk = false;
                        this.isRRCHO = false;
                        this.isDRCHCO = false;
                        this.isOthers = false;
                        this.facilityCode = "00000000";
                        break;

                    case "crs":
                    case "ima":
                    case "ihv":
                    case "cssc":
                    case "others":
                    case "other":
                        this.isDataClerk = false;
                        this.isDRCHCO = false;
                        this.isRRCHO = false;
                        this.isOthers = true;
                        this.isSI = false;
                        this.facilityCode = "00000000";
                        break;

                    default:
                        this.hasError = true;
                        this.invalid = false;
                        this.errorCode = RegistrationError.INVALID_FACILITY;
                        this.errorTextLabelID = INVALID_FACILITY_LABEL;
                        this.errorText = Utilities.GetTranslationText(this.senderNum, INVALID_FACILITY_LABEL);
                        break;
                }
            }
        }

        private void ValidateUsername(string username)
        {
            // Validating username length
            if (username.Length > MAX_USERNAME_LENGTH)
            {
                this.hasError = true;
                this.invalid = false;
                this.errorCode = RegistrationError.LONG_USERNAME;
                this.errorTextLabelID = LONG_USERNAME_LABEL;
                this.errorText = Utilities.GetTranslationText(this.senderNum, LONG_USERNAME_LABEL);
            }
            if (this.hasError) return;

            // Validating: Username must include at least two names
            string[] names = username.Split(' ');
            if (names.Length < 2)
            {
                this.hasError = true;
                this.invalid = false;
                this.errorCode = RegistrationError.SHORT_USERNAME;
                this.errorTextLabelID = SHORT_USERNAME_LABEL;
                this.errorText = Utilities.GetTranslationText(this.senderNum, SHORT_USERNAME_LABEL);
                return;
            }
            this.fullname = username;
        }

        /// <summary>
        /// Register new user to the system
        /// </summary>
        /// <returns>True if use is registered otherwise false</returns>
        private bool RegisterUser()
        {
            SqlConnection connection = new SqlConnection();
            try
            {
                connection.ConnectionString = AppConfiguration.DBConnectionString;
                connection.Open();

                SqlCommand command = connection.CreateCommand();
                command.CommandType = CommandType.Text;
                command.CommandText = @"INSERT INTO [ApprovedSMSCells] ([CellNum],[Name], [LangID],
                    [facCode],[SendSMS], [Email], [TitleID])
                    VALUES (@SenderNum, @Details,@LangID, @FacCode,'TRUE', @Email, @TitleID)";

                SqlParameter param = command.CreateParameter();
                param.ParameterName = "@Sendernum";
                param.DbType = DbType.String;
                param.Value = this.senderNum;
                command.Parameters.Add(param);

                param = command.CreateParameter();
                param.ParameterName = "@Details";
                param.DbType = DbType.String;
                param.Value = this.fullname;
                command.Parameters.Add(param);

                param = command.CreateParameter();
                param.ParameterName = "@LangID";
                param.DbType = DbType.Int32;
                param.Value = this.language;
                command.Parameters.Add(param);

                param = command.CreateParameter();
                param.ParameterName = "@FacCode";
                param.DbType = DbType.String;
                param.Value = this.facilityCode;
                command.Parameters.Add(param);

                param = command.CreateParameter();
                param.ParameterName = "@Email";
                param.DbType = DbType.String;
                if (String.IsNullOrEmpty(this.emailAddress)) param.Value = DBNull.Value;
                else param.Value = this.emailAddress;
                command.Parameters.Add(param);

                if (this.isDataClerk) this.title = UserTitle.DATA_CLERK;
                if (this.isDRCHCO) this.title = UserTitle.DRCHCO;
                if (this.isRRCHO) this.title = UserTitle.RRCHCO;
                if (this.isOthers) this.title = UserTitle.OTHERS;
                if (this.isSI) this.title = UserTitle.SI;

                param = command.CreateParameter();
                param.ParameterName = "@TitleID";
                param.DbType = DbType.Int32;
                param.Value = this.title;
                command.Parameters.Add(param);

                int affectedRows = command.ExecuteNonQuery();
                connection.Close();
                return (affectedRows > 0);
            }
            catch
            {
                return false;
            }
            finally
            {
                if (connection.State == ConnectionState.Open) connection.Close();
            }
        }

        /// <summary>
        /// Update user registration details
        /// </summary>
        /// <returns>True if user details were modified otherwise false</returns>
        private bool UpdateUserRegistration()
        {
            SqlConnection connection = new SqlConnection();
            try
            {
                connection.ConnectionString = AppConfiguration.DBConnectionString;
                connection.Open();

                SqlCommand command = connection.CreateCommand();
                command.CommandType = CommandType.Text;
                command.CommandText = @"UPDATE [ApprovedSMSCells] SET [Name]=@Details, [LangID]=@LangID, 
                    [facCode]=@FacCode, [Email]=@Email, [TitleID]=@TitleID WHERE [CellNum]=@SenderNum";

                SqlParameter param = command.CreateParameter();
                param.ParameterName = "@Sendernum";
                param.DbType = DbType.String;
                param.Value = this.senderNum;
                command.Parameters.Add(param);

                param = command.CreateParameter();
                param.ParameterName = "@Details";
                param.DbType = DbType.String;
                param.Value = this.fullname;
                command.Parameters.Add(param);

                param = command.CreateParameter();
                param.ParameterName = "@LangID";
                param.DbType = DbType.Int32;
                param.Value = this.language;
                command.Parameters.Add(param);

                param = command.CreateParameter();
                param.ParameterName = "@FacCode";
                param.DbType = DbType.String;
                param.Value = this.facilityCode;
                command.Parameters.Add(param);

                param = command.CreateParameter();
                param.ParameterName = "@Email";
                param.DbType = DbType.String;
                if (String.IsNullOrEmpty(this.emailAddress)) param.Value = DBNull.Value;
                else param.Value = this.emailAddress;
                command.Parameters.Add(param);

                if (this.isDataClerk) this.title = UserTitle.DATA_CLERK;
                if (this.isDRCHCO) this.title = UserTitle.DRCHCO;
                if (this.isRRCHO) this.title = UserTitle.RRCHCO;
                if (this.isOthers) this.title = UserTitle.OTHERS;
                if (this.isSI) this.title = UserTitle.SI;

                param = command.CreateParameter();
                param.ParameterName = "@TitleID";
                param.DbType = DbType.Int32;
                param.Value = this.title;
                command.Parameters.Add(param);

                int affectedRows = command.ExecuteNonQuery();
                connection.Close();
                return (affectedRows > 0);
            }
            catch
            {
                return false;
            }
            finally
            {
                if (connection.State == ConnectionState.Open) connection.Close();
            }
        }

        /// <summary>
        /// Remove user's registration
        /// </summary>
        [Obsolete("User static method RemoveUserRegistration")]
        private void RemoveUser()
        {
            SqlConnection connection = new SqlConnection();
            try
            {
                connection.ConnectionString = AppConfiguration.DBConnectionString;
                connection.Open();

                SqlCommand command = connection.CreateCommand();
                command.CommandType = CommandType.Text;
                command.CommandText = @"DELETE FROM [ApprovedSMSCells] WHERE [CellNum] = @SenderNum";

                SqlParameter param = command.CreateParameter();
                param.ParameterName = "@Sendernum";
                param.DbType = DbType.String;
                param.Value = this.senderNum;
                command.Parameters.Add(param);

                command.ExecuteNonQuery();
                connection.Close();
            }
            catch { }
            finally
            {
                if (connection.State == ConnectionState.Open) connection.Close();
            }
        }

        /// <summary>
        /// Checks if the user is registered for the service
        /// </summary>
        /// <returns>True if user is registered otherwise false</returns>
        private bool IsUserRegistered()
        {
            SqlConnection connection = new SqlConnection();
            try
            {
                connection.ConnectionString = AppConfiguration.DBConnectionString;
                connection.Open();

                SqlCommand command = connection.CreateCommand();
                command.CommandType = CommandType.Text;
                command.CommandText = @"SELECT COUNT([CellNum]) AS [Count] FROM [ApprovedSMSCells] WHERE [CellNum] = @SenderNum";

                SqlParameter param = command.CreateParameter();
                param.ParameterName = "@Sendernum";
                param.DbType = DbType.String;
                param.Value = this.senderNum;
                command.Parameters.Add(param);

                string output = command.ExecuteScalar().ToString();
                connection.Close();
                int count = int.Parse(output);
                return (count == 1);
            }
            catch 
            {
                return false;
            }
            finally
            {
                if (connection.State == ConnectionState.Open) connection.Close();
            }
        }
        #endregion
    }
}
